package com.ssyt.hiring.company.service;

import java.util.ArrayList;
import java.util.List;

import com.ssyt.hiring.common.PageModel;
import com.ssyt.hiring.common.util.DBUtil;
import com.ssyt.hiring.company.entity.PositionEntity;

/**
 * Position Dao
 * 
 * @author Administrator
 *
 */
public class PositionService {

	/**
	 * 职位列表
	 * 
	 * @return
	 */
	public PageModel<PositionEntity> queryAll(int pageNo, int pageSize) {
		String sql = "SELECT c.name as 'company$name', c.address as 'company$address', c.slogan as 'company$slogan', p.id, p.name, p.salary1, p.salary2, p.release_time FROM company c, position p WHERE c.id=p.company_id ORDER BY p.id DESC LIMIT ?,?";
		List<PositionEntity> list = DBUtil.executeQuery(PositionEntity.class,
				sql, (pageNo - 1) * pageSize, pageSize);
		return new PageModel<PositionEntity>(pageNo, pageSize, getAllRecords(),
				list);
	}

	/**
	 * 职位列表
	 * 
	 * @param keyWord
	 * 
	 * @return
	 */
	public List<PositionEntity> loadingData(int pageNo, int pageSize,
			String keyWord) {
		String sql = "SELECT c.name as 'company$name', c.address as 'company$address', c.slogan as 'company$slogan', p.id, p.name, p.salary1, p.salary2, p.release_time FROM company c, position p WHERE c.id=p.company_id ORDER BY p.id DESC LIMIT ?,?";
		List<PositionEntity> list = DBUtil.executeQuery(PositionEntity.class,
				sql, (pageNo - 1) * pageSize, pageSize);
		return list;
	}

	/**
	 * 查询position总记录数
	 * 
	 * @return
	 */
	public int getAllRecords() {
		return DBUtil.getAllRecords("SELECT COUNT(*) FROM position");
	}

	/**
	 * 查询position总记录数
	 * 
	 * @return
	 */
	public int getAllRecords(String keyWord) {
		return DBUtil
				.getAllRecords(
						"SELECT COUNT(*) FROM company c, position p WHERE c.id=p.company_id AND p.name LIKE ? OR c.name LIKE ?",
						"%" + keyWord + "%", "%" + keyWord + "%");
	}

	/**
	 * 职位详细信息
	 * 
	 * @param id
	 * @return
	 */
	public PositionEntity detail(int id) {
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT ");
		sb.append("c.name as 'company$name', ");
		sb.append("c.email as 'company$email', ");
		sb.append("c.phone as 'company$phone', ");
		sb.append("c.linkman as 'company$linkman', ");
		sb.append("c.address as 'company$address', ");
		sb.append("c.numbers as 'company$numbers', ");
		sb.append("c.slogan as 'company$slogan', ");
		sb.append("p.id, ");
		sb.append("p.name, ");
		sb.append("p.duty, ");
		sb.append("p.ability, ");
		sb.append("p.salary1, ");
		sb.append("p.salary2, ");
		sb.append("p.release_time ");
		sb.append("FROM company c, position p ");
		sb.append("WHERE c.id=p.company_id AND p.id=? ");
		return DBUtil.getUniqueResult(PositionEntity.class, sb.toString(), id);
	}

	/**
	 * 某公司职位列表
	 * 
	 * @param id
	 * @return
	 */
	public List<PositionEntity> list(int id) {
		String sql = "SELECT * FROM position WHERE company_id=? order by id DESC";
		return DBUtil.executeQuery(PositionEntity.class, sql, id);
	}

	/**
	 * 发布职位
	 * 
	 * @param list
	 * @return
	 */
	public int add(List<Object> list) {
		String sql = "INSERT INTO position VALUES(null,?,?,?,?,?,?,NOW())";
		return DBUtil.executeUpdate(sql, list);
	}

	/**
	 * 删除职位
	 * 
	 * @param id
	 * @return
	 */
	public int delete(int id) {
		String sql = "DELETE FROM position WHERE id=?";
		return DBUtil.executeUpdate(sql, id);
	}

	/**
	 * 根据id 查询职位信息
	 * 
	 * @param id
	 * @return
	 */
	public PositionEntity queryById(int id) {
		String sql = "SELECT * FROM position WHERE id=? LIMIT 1";
		return DBUtil.getUniqueResult(PositionEntity.class, sql, id);

	}

	/**
	 * 修改职位信息
	 * 
	 * @param list
	 * @return
	 */
	public int update(List<Object> list) {
		String sql = "UPDATE position SET company_id=?,name=?,duty=?,ability=?,salary1=?,salary2=?,release_time=NOW() WHERE id=?";
		return DBUtil.executeUpdate(sql, list);
	}

	/**
	 * 按关键字查询记录
	 * 
	 * @param pageNo
	 * @param pageSize
	 * @return
	 */
	public PageModel<PositionEntity> queryByKeyWord(int pageNo, int pageSize,
			String keyWord) {
		List<Object> list = new ArrayList<>();
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT c.name as 'company$name', c.address as 'company$address', c.slogan as 'company$slogan', p.id, p.name, p.salary1, p.salary2, p.release_time FROM company c, position p WHERE c.id=p.company_id ");
		if (keyWord != null && !"".equals(keyWord)) {
			sb.append("AND p.name LIKE ? OR c.name LIKE ? ");
			list.add("%" + keyWord + "%");
			list.add("%" + keyWord + "%");
		}
		sb.append("ORDER BY p.id DESC LIMIT ?,?");
		list.add((pageNo - 1) * pageSize);
		list.add(pageSize);
		List<PositionEntity> positionList = DBUtil.executeQuery(
				PositionEntity.class, sb.toString(), list);
		return new PageModel<PositionEntity>(pageNo, pageSize,
				getAllRecords(keyWord), positionList);
	}
}
